DROP TABLE IF EXISTS jms_ods.yl_tmsnew_tms_shipment;
CREATE EXTERNAL TABLE jms_ods.yl_tmsnew_tms_shipment(
 ID bigint comment "主键",  --NUMBER(11)
 SHIPMENT_NO string comment "任务编号",  --NVARCHAR2(30)
 VEHICLELINE_TYPE bigint comment "运营模式",  --NUMBER(11)
 BUSINESS_ATTRIBUTE bigint comment "业务属性 1:正班 2:加班",  --NUMBER(11)
 SHIPMENT_NAME string comment "任务名称",  --NVARCHAR2(100)
 VEHICLELINE_ID bigint comment "参考车线ID",  --NUMBER(11)
 VEHICLELINE_CODE string comment "参考车线编码",  --NVARCHAR2(30)
 VEHICLELINE_NAME string comment "参考车线名称",  --NVARCHAR2(100)
 SHIFTS bigint comment "班次",  --NUMBER(11)
 TRANSPORT_TYPE string comment "运力类型",  --NVARCHAR2(30)
 OPERATION_MODEL bigint comment "运行模式",  --NUMBER(11)
 VEHICLE_GROUPID bigint comment "规划车型组ID",  --NUMBER(11)
 VEHICLE_TYPEGROUP string comment "规划车型",  --NVARCHAR2(50)
 START_ID bigint comment "始发网点ID",  --NUMBER(11)
 START_CODE string comment "始发网点编号",  --NVARCHAR2(20)
 START_NAME string comment "始发网点名称",  --NVARCHAR2(50)
 END_ID bigint comment "目的网点ID",  --NUMBER(11)
 END_CODE string comment "目的网点编号",  --NVARCHAR2(20)
 END_NAME string comment "目的网点名称",  --NVARCHAR2(50)
 CNT bigint comment "途径地数量",  --NUMBER(11)
 MILEAGE double comment "系统里程(KM)",  --NUMBER
 TOTAL_RUNTIME bigint comment "总运行时间(MIN)",  --NUMBER(11)
 TOTAL_INTRANSITTIME bigint comment "总在途时间(MIN)",  --NUMBER(11)
 SPAN_DAYS bigint comment "跨越天数",  --NUMBER(11)
 STANDARD_SPEED double comment "标准时速(KM/H)",  --NUMBER
 PLANNED_DEPARTURE_TIME timestamp comment "规划发车时间",  --DATE
 PLANNED_ARRIVAL_TIME timestamp comment "规划到车时间",  --DATE
 ACTUAL_DEPARTURE_TIME timestamp comment "实际发车时间",  --DATE
 ACTUAL_ARRIVAL_TIME timestamp comment "实际到车时间",  --DATE
 SHIPMENT_STATE bigint comment "任务状态,待调度0 已调度1 待执行2 在途3 已完成4 已作废5",  --NUMBER(11)
 APPLY_REASON string comment "申请原因",  --NVARCHAR2(80)
 AUDIT_STATUS smallint comment "审核状态",  --NUMBER(4)
 AUDIT_REMARK string comment "审核备注",  --NVARCHAR2(80)
 AUDIT_ID bigint comment "审核人编号",  --NUMBER(11)
 AUDITER string comment "审核人",  --NVARCHAR2(30)
 AUDIT_DATE timestamp comment "审核时间",  --DATE
 VEHICLE_CLASS_ID string comment "车种类ID",  --NVARCHAR2(10)
 VEHICLE_CLASS_NAME string comment "车种类名称",  --NVARCHAR2(10)
 PLATE_NUMBER string comment "车牌号",  --NVARCHAR2(30)
 TRAILER_NUMBER string comment "挂车号",  --NVARCHAR2(30)
 CARRIER_ID bigint comment "承运商ID",  --NUMBER(11)
 CARRIER_TYPE smallint comment "承运商类型",  --NUMBER(4)
 CARRIER_NAME string comment "承运商全名",  --NVARCHAR2(50)
 VEHICLETYPE_ID string comment "车型ID",  --NVARCHAR2(50)
 VEHICLETYPE_NAME string comment "车型名称",  --NVARCHAR2(50)
 LOAD_WEIGHT double comment "核定载重(KG)",  --NUMBER
 VEHICLE_DOOR_CNT bigint comment "车门",  --NUMBER(11)
 DRIVER_NAME string comment "司机名称",  --NVARCHAR2(50)
 DRIVER_CONTACT string comment "随车电话",  --NVARCHAR2(20)
 SHIPMENT_TYPE bigint comment "任务类型 1:自有正班 2:外租正班 3:自有加班 4:外租加班",  --NUMBER(11)
 QUOTATION_MODEL bigint comment "报价模式 1 加班价 2 正班价",  --NUMBER(11)
 CREATE_BY bigint comment "创建人ID",  --NUMBER(11)
 CREATE_BY_NAME string comment "创建人姓名",  --NVARCHAR2(20)
 CREATE_TIME timestamp comment "创建时间",  --DATE
 UPDATE_BY bigint comment "更新人ID",  --NUMBER(11)
 UPDATE_BY_NAME string comment "更新人姓名",  --NVARCHAR2(20)
 UPDATE_TIME timestamp comment "更新时间",  --DATE
 APPLY_REASON_ITEM smallint comment "申请原因固定选项(1临时加班、2顶班、3增加经停、4取消经停、5线路延伸、6其他)",  --NUMBER(4)
 PIC_PATH string comment "微信上传任务图片（多张以,隔开）",  --NCLOB
 PIC_ADR string comment "前端图片地址（多张以,隔开）",  --NVARCHAR2(650)
 PROMOTION tinyint comment "是否促销：1:是，2：否",  --NUMBER(1)
 OVERTIME_TYPE tinyint comment "加班类型：11 爆仓加班、12 顶班、13 应急加班",  --NUMBER(2)
 OVERTIME_REASONS string comment "加班原因说明",  --NVARCHAR2(200)
 ORI_REG_SHIFT_CARRIER_ID bigint comment "原正班承运商ID",  --NUMBER(11)
 FREIGHT_GENERATE_STATUS tinyint comment "运费流水生成状态：1 已生产 2 未生成",  --NUMBER(2)
 BILLING_WAY tinyint comment "计费方式（1：整车计重、2：零担计重、3：按趟计费）",  --NUMBER(2)
 SETTLEMENT_VEHICLE_TYPE_ID string comment "结算车型ID",  --NVARCHAR2(50)
 SETTLEMENT_VEHICLE_TYPE_NAME string comment "结算车型名称",  --NVARCHAR2(100)
 CAR_APPLY_CODE string comment "用车申请编码",  --NVARCHAR2(100)
 BIDDING_DEMAND_CODE string comment "招标需求编码"  --NVARCHAR2(100)
 )
 COMMENT '运单'
 PARTITIONED BY (dt STRING COMMENT '更新/创建日期 (yyyy-MM-dd)')
 STORED AS PARQUET
 LOCATION '/dw/hive/jms_ods.db/external/yl_tmsnew_tms_shipment'
 TBLPROPERTIES (
 'discover.partitions'='false',
 'parquet.column.index.access'='true'
 );

MSCK REPAIR TABLE jms_ods.yl_tmsnew_tms_shipment;